ajDBMergeRecord function

Available since AlchemyJ v4.1

Description

The ajDBMergeRecord function is used for updating records in a database table if it exists. Otherwise, it will be created. Please take note that to run this function from Excel, you need to set up the Data Source Connection in ##ExternalResources and config the table in DB Schema.

Syntax

ajDBMergeRecord( table_name, column_headers, data, table_schema, [data_source_id], [run_condition], [run_by_function_point_only] )

Argument Name	Argument Type	Description
table_name (required)	String	The name of the table to be updated.
column_headers (required)	Range / Array	The columns to be updated. It could be a range of single row or column where each cell is a table column name or a data name defined in DB Schema. CLOB and BLOB are not supported.
data (required)	Range / Array	The range where data is located. The orientation can either be landscape or portrait. The orientation should be the same as column_headers.
table_schema (required)	Range / Array	The range that defines the DB Schema.
data_source_id (optional)	String	The data source shall be used in this database operation. It shall be defined in ##ExternalResources worksheet. The default value is "primary".
run_condition (optional)	Boolean	The function will run when the value is TRUE. Otherwise, it will not run. The default value is TRUE.
run_by_function_point_only (optional)	Boolean	If it equals FALSE, the function can be executed through ‘Excel Calculation’ (can be either Automatic or Manual, Calculate Now or Calculate Sheet) or Preview Function Point. If it equals TRUE, the function can be executed with Preview Function Point (AlchemyJ ribbon \ Preview Function Point) only. The default value is TRUE.

The function will return:

1) Return Value: Number of affected records.

2) Return Type: Single Value / Multiple values (array formula)

Example

Before using the function, you need to set up the Data Source Connection in ##ExternalResources worksheet and config the table in DBSchema worksheet.

Before running this function point, you need to configure the Data Source ID in ##RestEndpointGroup worksheet or ##JavaApiClass.

We will use the following table in our examples. The table name is tb_customer. It has 5 columns and 5 rows.

Example 1 - Create record

NAME in this example is defined as a key in DBSchema. ajDBMergeRecord looks up CUSTOMER_NAME from tb_customer that is MICKY MAI, but it is not found. Therefore, a new record with the data is created.

Check the result from DB.

Example 2 - Update Record

In the example, the records can be found, therefore it is updated with the data provided instead. The column_headers is the Data Name that is defined in DB Schema.

Click Add Component to and select DB Schema to add the DB Schema and input the table name.

Check the result from DB.

Click here to download the use case workbooks for further reference.

You can also check the video help from Youtube!

## Error Scenarios

It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).

Error Scenario
DB connection error.
No Key order value defined in DB Schema.
Number of columns in column_headers does not match with the number of data columns.
Table name does not exist in provided DB Schema.
Table name is empty.
The primary key value is empty.
The provided column header cannot be found in the specified table schema range.
The table schema columns are invalid.
The table schema range does not include a header or a row of data.